#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# vim: set et sw=4 ts=4 sts=4 ff=unix fenc=utf8:
# Author: Binux<i@binux.me>
#         http://binux.me
# Created on 2014-11-05 10:42:24
#
#使用方法：
#    1, 把本文件放到Python27\Lib\site-packages\pyspider/database/mysql/目录下命名为xxx.py;
#    2, 建立相应的表和库;
#    3, 在脚本文件里使用from pyspider.database.mysql.xxx import ToMysql引用本代码;
#    4, 重写pyspider---on_result方法.

from six import itervalues
import MySQLdb


class ToMySql(object):
    def __init__(self,kwargs):
        '''kwargs = {'host':'localhost',
            'user':'root',
            'password':'xxcaonima',
            'db':'jd',
            'char':'utf-8'
        }'''
        hosts = kwargs['host']
        username = kwargs['user']
        password = kwargs['password']
        database = kwargs['db']
        charcter = kwargs['char']

        self.connection = False
        
        try:
            self.conn = MySQLdb.connect(host=hosts,port=3306,user=username,passwd=password,db=database,charset=charcter)
            self.cursor = self.conn.cursor()
            self.cursor.execute("set names utf8mb3")
            self.connection = True
        except Exception,e:
            print 'Fail To Connect Database',e

    def escape(self,string):
        return '%s' % string

    def insert(self,tablename = None, **values):
        if self.connection:
            tablename = self.escape(tablename)
            if values:
                #取出return的表的key和value
                keys = ",".join(self.escape(k) for k in values['table'])
                value = ",".join(['%s',]*len(values['table']))
                sql_query = "insert into %s (%s) values (%s)" % (tablename, keys, value)
            else:
                sql_query = "replace into %s default values ", tablename
            #执行语句
            try:
                if values:
                    self.cursor.execute(sql_query, list(itervalues(values['table'])))
                else:
                    self.cursor.execute(sql_query)
                self.conn.commit()
                return True
            except Exception,e:
                print "Have An Error: ",e
                return False

    def update(self,tablename = None, **values):
        if self.connection:
            tablename = self.escape(tablename)
            if values:
                #取出return的表的key和value
                str = ''
                str1 = []
                for k in values['table']:
                    str = k + ' = "' + '' + values['table'][k] + '"'
                    str1.append(str)
                sql_query = "update %s set %s where ID =  '%s'" % (tablename, ' , '.join(str1), values['ID'])
                print sql_query
            else:
                sql_query = "replace into %s default values ", tablename
            #执行语句
            try:
                if values:
                    self.cursor.execute(sql_query)
                else:
                    self.cursor.execute(sql_query)
                self.conn.commit()
                return True
            except Exception, e:
                print "Have An Error: ", e
                return False

# class main():
#     result = {
#         'table': {
#             'id': 55,
#             'name': '打到',
#             'addre': '挡刀挡刀',
#         }
#         }
#     result1 = { 'id': 15,
#         'table':{
#         'name': 'aa',
#         'addre': '挡刀挡刀',
#         }
#     }
#     kwargs = {'host': 'localhost',
#               'user': 'root',
#               'password': 'xxcaonima',
#               'db': 'jd',
#               'char': 'utf8'
#               }
#     sql = ToMySql(kwargs)
#     sql.insert('test', **result)
#     #sql.update('test', **result1)
#
# if __name__ == "__main__":
#     main()
